package com.jibei.mendo.utils;

import android.content.Context;
import android.content.Intent;
import android.net.Uri;
import android.os.Environment;
import android.util.Log;

import com.blankj.utilcode.util.FileUtils;
import com.blankj.utilcode.util.ToastUtils;
import com.jibei.mendo.bean.ConfigInfoBean;
import com.jibei.mendo.bean.IgnitionInfoBean;
import com.jibei.mendo.bean.MatrixInfoBean;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import static com.jibei.mendo.utils.Constant.EXCEL_DIR;
import static com.jibei.mendo.utils.Constant.FIRMWARE_DIR;

/**
 * Created by lhl on 2019/8/17.
 */
public class ExcelUtil {

    private ExcelUtil() {
    }

    public static ExcelUtil getInstance() {
        return ExcelUtil.Holder.instance;
    }

    private static class Holder {
        private static final ExcelUtil instance = new ExcelUtil();
    }

    /**
     * 读取Excel某个sheet的最后一行第一列的数据
     *
     * @param mContext 上下文
     * @param sheetNum 第几张工作表
     */
    public synchronized String readFromExcelItemInfo(Context mContext, String fileName, int sheetNum) {
        String itemStr = "";
        try {
            String excelPath = getExcelPath() + "/" + fileName;
            if (excelPath == null) {
                return null;
            }

            InputStream stream = new FileInputStream(excelPath);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            int rowsCount = sheet.getPhysicalNumberOfRows();//行数
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            if (rowsCount > 0) {
                Row row = sheet.getRow(rowsCount - 1);
                itemStr = getCellAsString(row, 0, formulaEvaluator);
            } else {
                ToastUtils.showShort("第" + (sheetNum + 1) + "张工作表为空！");
            }
        } catch (Exception e) {
            Log.e("lhl", "信息工作表异常>>>" + e.getMessage());
            e.printStackTrace();
        }
        return itemStr;
    }

    /**
     * 读取Excel主机基本数据和主机ID数据
     *
     * @param mContext 上下文
     * @param sheetNum 第几张工作表
     */
    public synchronized String readFromExcelDeviceInfo(Context mContext, String fileName, int sheetNum) {
        String DevInf = "";
        try {
            String excelPath = getExcelPath() + "/" + fileName;
            if (excelPath == null) {
                return null;
            }

            InputStream stream = new FileInputStream(excelPath);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            int rowsCount = sheet.getPhysicalNumberOfRows();//行数
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            if (rowsCount > 0) {
                //0 row 1 cell = ID
                //1 row 1 cell = role
                Row rowid = sheet.getRow(0);//首行
                Row rowRole = sheet.getRow(1);//首行
//                    int cellsCountid = rowid.getPhysicalNumberOfCells();//列数
                String ID_LOCAL = getCellAsString(rowid, 1, formulaEvaluator);// ID
                String ROLE_LOCAL = getCellAsString(rowRole, 1, formulaEvaluator);// Role
                try {
                    if (ID_LOCAL.contains(".")) {
                        int indexOf = ID_LOCAL.indexOf(".");
                        ID_LOCAL = ID_LOCAL.substring(0, indexOf);
                    }
                    int matchIDInt = Integer.parseInt(ID_LOCAL);
                    if (ROLE_LOCAL.contains(".")) {
                        int indexOf = ROLE_LOCAL.indexOf(".");
                        ROLE_LOCAL = ROLE_LOCAL.substring(0, indexOf);
                    }
                    int matchRoleInt = Integer.parseInt(ROLE_LOCAL);
                    Log.d("lhl", "Info:" + matchIDInt + "," + matchRoleInt);

                    DevInf = matchIDInt + "," + matchRoleInt;
                }
                catch(Exception e)
                {
                    e.printStackTrace();
                }


            } else {
                ToastUtils.showShort("设备基础信息工作表为空！");
            }
        } catch (Exception e) {
            Log.e("lhl", "设备基础息工作表异常>>>" + e.getMessage());
            e.printStackTrace();
        }
        return DevInf;
    }

    /**
     * 读取Excel矩阵信息数据
     *
     * @param mContext 上下文
     * @param sheetNum 第几张工作表
     */
    public synchronized List<MatrixInfoBean> readFromExcelMatrixInfo(Context mContext, String fileName, int sheetNum) {
        List<MatrixInfoBean> matrixInfoBeanList = new ArrayList<>();
        try {
            String excelPath = getExcelPath() + "/" + fileName;
            if (excelPath == null) {
                return null;
            }

            InputStream stream = new FileInputStream(excelPath);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            int rowsCount = sheet.getPhysicalNumberOfRows();//行数
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            if (rowsCount > 0) {
                for (int r = 0; r < rowsCount; r++) {
                    MatrixInfoBean matrixInfoBean = new MatrixInfoBean();
                    List<String> stringList = new ArrayList<>();
                    Row row = sheet.getRow(r);
                    int cellsCount = row.getPhysicalNumberOfCells();//列数
                    for (int c = 0; c < cellsCount; c++) {
                        String value = getCellAsString(row, c, formulaEvaluator);
                        if (value != null) {
                            if (c == 0) {
                                matrixInfoBean.setMatrixId(value);
                            } else {
                                stringList.add(value);
                            }
                            //                        String cellInfo = "行数=:" + r + "; 列数=:" + c + "; 值=:" + value;
                            //                        Log.d("lhl", "读取的Excel信息>>>" + cellInfo);
                        }
                    }
                    matrixInfoBean.setVidLists(stringList);
                    matrixInfoBeanList.add(matrixInfoBean);
                }
            } else {
                ToastUtils.showShort("矩阵信息工作表为空！");
            }
        } catch (Exception e) {
            Log.e("lhl", "矩阵信息工作表异常>>>" + e.getMessage());
            e.printStackTrace();
        }
        return matrixInfoBeanList;
    }

    /**
     * 读取Excel点火信息数据
     *
     * @param mContext 上下文
     * @param sheetNum 第几张工作表
     */
    public synchronized List<IgnitionInfoBean> readFromExcelIgnitionInfo(Context mContext, String fileName, int sheetNum) {
        List<IgnitionInfoBean> ignitionInfoBeanList = new ArrayList<>();
        try {
            String excelPath = getExcelPath() + "/" + fileName;
            if (excelPath == null) {
                return null;
            }

            InputStream stream = new FileInputStream(excelPath);
            //                                InputStream stream = mContext.getResources().openRawResource(R.raw.test);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            int rowsCount = sheet.getPhysicalNumberOfRows();//行数
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            if (rowsCount > 0) {
                for (int r = 0; r < rowsCount; r++) {
                    IgnitionInfoBean ignitionInfoBean = new IgnitionInfoBean();
                    Row row = sheet.getRow(r);
                    int cellsCount = row.getPhysicalNumberOfCells();//列数
                    for (int c = 0; c < cellsCount; c++) {
                        String value = getCellAsString(row, c, formulaEvaluator);
                        switch (c) {
                            case 0:
                                ignitionInfoBean.setTime(value);
                                break;
                            case 1:
                                ignitionInfoBean.setCommand(value);
                                break;
                            default:
                                break;
                        }
                        //                        String cellInfo = "行数=:" + r + "; 列数=:" + c + "; 值=:" + value;
                        //                        Log.d("lhl", "读取的Excel信息>>>" + cellInfo);
                    }
                    ignitionInfoBeanList.add(ignitionInfoBean);
                }
            } else {
                ToastUtils.showShort("点火信息工作表为空！");
            }
        } catch (Exception e) {
            Log.e("lhl", "点火信息工作表异常>>>" + e.getMessage());
            e.printStackTrace();
        }
        return ignitionInfoBeanList;
    }

    /**
     * 读取Excel配置信息工作表的数据
     *
     * @param mContext 上下文
     * @param sheetNum 第几张工作表
     */
    public synchronized List<ConfigInfoBean> readFromExcelConfigInfo(Context mContext, String fileName, int sheetNum) {
        List<ConfigInfoBean> configInfoBeanList = new ArrayList<>();
        try {
            String excelPath = getExcelPath() + "/" + fileName;
            if (excelPath == null) {
                return null;
            }

            InputStream stream = new FileInputStream(excelPath);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            int rowsCount = sheet.getPhysicalNumberOfRows();//行数
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            if (rowsCount > 0) {
                for (int r = 0; r < rowsCount; r++) {
                    ConfigInfoBean configInfoBean = new ConfigInfoBean();
                    StringBuilder resultStr = new StringBuilder();
                    StringBuilder checkStr = new StringBuilder();
                    Row row = sheet.getRow(r);
                    int cellsCount = row.getPhysicalNumberOfCells();//列数
                    for (int c = 0; c < cellsCount; c++) {
                        String value = getCellAsString(row, c, formulaEvaluator);
                        if (c == 0) {
                            configInfoBean.setVid(value);
                        } else {
                            resultStr.append(value);
                            if (value.equals("0000")) {
                                checkStr.append("0");
                            } else {
                                checkStr.append("1");
                            }
                        }
                        //                        String cellInfo = "行数=:" + r + "; 列数=:" + c + "; 值=:" + value;
                        //                        Log.d("lhl", "读取的Excel信息>>>" + cellInfo);
                    }
                    configInfoBean.setConfigInfo("F0" + configInfoBean.getVid() + "41E9" + resultStr.toString());
                    configInfoBean.setCheckInfo(checkStr.toString());
                    configInfoBeanList.add(configInfoBean);
                }
            } else {
                ToastUtils.showShort("配置信息工作表为空！");
            }
        } catch (Exception e) {
            Log.e("lhl", "配置信息工作表异常>>>" + e.getMessage());
            e.printStackTrace();
        }
        return configInfoBeanList;
    }

    /**
     * 写入Excel数据
     *
     * @param mContext 上下文
     */
    public void writeToExcel(Context mContext) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("mysheet"));
        for (int i = 0; i < 10; i++) {
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(i);
        }
        String outFileName = "filetoshare.xlsx";
        try {
            File cacheDir = mContext.getCacheDir();
            File outFile = new File(cacheDir, outFileName);
            OutputStream outputStream = new FileOutputStream(outFile.getAbsolutePath());
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            share(outFileName, mContext.getApplicationContext());
        } catch (Exception e) {
            Log.e("lhl", "写入Excel数据失败");
            e.printStackTrace();
        }
    }

    /**
     * @return 得到excel文件夹的路径
     */
    public String getExcelPath() {
        if (Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
            String excelPath = EXCEL_DIR;
            boolean isExists = com.blankj.utilcode.util.FileUtils.isFileExists(excelPath);
            if (!isExists) {
                Log.e("lhl", "excel文件夹不存在！");
                ToastUtils.showShort("excel文件夹不存在！");
                return null;
            }
            return excelPath;
        } else {
            ToastUtils.showShort("SD卡不存在！");
            return null;
        }
    }

    /**
     * @return 得到固件文件夹的路径
     */
    public String getFirmwarePath() {
        if (Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
            String FirmwarePath = FIRMWARE_DIR;
            boolean isExists = com.blankj.utilcode.util.FileUtils.isFileExists(FirmwarePath);
            if (!isExists) {
                Log.e("lhl", "固件文件夹不存在！");
                ToastUtils.showShort("固件文件夹不存在！");
                return null;
            }
            return FirmwarePath;
        } else {
            ToastUtils.showShort("SD卡不存在！");
            return null;
        }
    }

    /**
     * @return 得到mendo.xlsx的路径
     */
    public String getExcelFilePath() {
        if (Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
            File sdCardDir = Environment.getExternalStorageDirectory();
            String excelPath = EXCEL_DIR + "/mendo.xlsx";
            boolean isExists = FileUtils.isFileExists(excelPath);
            if (!isExists) {
                Log.e("lhl", "mendo.xlsx不存在！");
                ToastUtils.showShort("mendo.xlsx不存在！");
                return null;
            }
            return excelPath;
        } else {
            ToastUtils.showShort("SD卡不存在！");
            return null;
        }
    }

    private String getCellAsString(Row row, int c, FormulaEvaluator formulaEvaluator) {
        String value = "";
        try {
            Cell cell = row.getCell(c);
            CellValue cellValue = formulaEvaluator.evaluate(cell);
            if (cellValue == null) {
                return null;
            }
            switch (cellValue.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = "" + cellValue.getBooleanValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double numericValue = cellValue.getNumberValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        double date = cellValue.getNumberValue();
                        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yy");
                        value = formatter.format(HSSFDateUtil.getJavaDate(date));
                    } else {
                        value = "" + numericValue;
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = "" + cellValue.getStringValue();
                    break;
                default:
            }
        } catch (NullPointerException e) {
            Log.e("lhl", "Excel空指针异常");
            e.printStackTrace();
        }
        return value;
    }

    private void share(String fileName, Context context) {
        Uri fileUri = Uri.parse("content://" + context.getPackageName() + "/" + fileName);
        Log.d("lhl", "sending" + fileUri.toString() + "...");
        Intent shareIntent = new Intent();
        shareIntent.setAction(Intent.ACTION_SEND);
        shareIntent.putExtra(Intent.EXTRA_STREAM, fileUri);
        shareIntent.setType("application/octet-stream");
    }
}
